<?php
include_once dirname(__FILE__)."/../database/database.class.php";

class SQLgenerator
{
  public static function createOrderSQL($orderfields)
  {
    $orderSQL = "";
    foreach($orderfields as $orderfield)
    {

      if(!is_array($orderfield) || empty($orderfield['column']) || empty($orderfield['direction']))
      {
        continue;
      }

      if(preg_match("/`.+`\.`.+`/", $orderfield['column']) == 0)
      {
        continue;
      }

      switch($orderfield['direction'])
      {
        case("DESC"):
          break;
        default:
          $orderfield['direction'] = "ASC";
          break;
      }

      if(!empty($orderSQL))
      {
        $orderSQL .= ",";
      }
      $orderSQL .= " ".$orderfield['column']." ".$orderfield['direction'];
    }

    if(!empty($orderSQL))
    {
      return "ORDER BY $orderSQL";
    }
    return "";
  }

  public static function createPageSQL($limit)
  {
    if(!is_array($limit)
        || (!isset($limit['page']) || !is_numeric($limit['page']))
        || (empty($limit['pagelimit']) || !is_numeric($limit['pagelimit']))
      )
    {
      return "";
    }
    
    return "LIMIT ".$limit['page']*$limit['pagelimit'].", ".$limit['pagelimit'];
  }

  public function createCardnameSQL($cardname)
  {
    global $db;

    $cardname = $db->escape_string($cardname);

    $name_finder = "`Card_details`.`name` LIKE \"%$cardname%\"";

    if(($cardname_special_char = str_replace("ae", "&#198;", $cardname)) != $cardname)
    {
      $cardname_special_char = $db->escape_string($cardname_special_char);
      $name_finder .= "OR `Card_details`.`name` LIKE \"%$cardname_special_char%\"";
    }

    return $name_finder;
  }

  public function createExpansionSQL($expansions)
  {
    global $db;

    if(!empty($expansions) && is_array($expansions))
    {
      $expansion_finder = "";
      foreach($expansions as $expansion)
      {
        $expansion = $db->escape_string($expansion);

        if(!empty($expansion_finder))
        {
          $expansion_finder .= " OR ";
        }
        $expansion_finder .= "`Card`.`expansion` = \"".$expansion."\"";
      }
      return $expansion_finder;
    }
    else
    {
      return "1";
    }
  }

  public function createCollectionSQL($collections)
  {
    if(!empty($collections) && is_array($collections))
    {
      $collection_finder = "`Card`.`ID` IN (SELECT `collection_card`.`card_ID` FROM `collection_card` WHERE ";

      $operator = "OR";
      if(isset($collections['operator']))
      {
        switch($collections['operator'])
        {
          case("XOR"):
          case("AND"):
            $operator = $collections['operator'];
            break;
          default:
            break;
        }
      }
      unset($collections['operator']);

      $condition = "";
      foreach($collections as $collection)
      {
        if(!empty($condition))
        {
          $condition .= " $operator ";
        }
        $condition .= "`collection_card`.`collection_ID` = \"$collection\"";
      }
      return $collection_finder.$condition.")";
    }
    else
    {
      return 0;
    }
  }

  public function createDeckSQL($decks)
  {
    if(!empty($decks) && is_array($decks))
    {
      $deck_finder = "`Card`.`ID` IN (SELECT `collection_card`.`card_ID` FROM `collection_card` WHERE ";

      $operator = "OR";
      if(isset($decks['operator']))
      {
        switch($decks['operator'])
        {
          case("XOR"):
          case("AND"):
            $operator = $decks['operator'];
            break;
          default:
            break;
        }
      }
      unset($decks['operator']);

      $condition = "";
      foreach($decks as $deck)
      {
        if(!empty($condition))
        {
          $condition .= " $operator ";
        }
        $condition .= "`collection_card`.`collection_ID` = \"$deck\"";
      }
      return $deck_finder.$condition.")";
    }
    else
    {
      return 0;
    }
  }

  public function createColorSQL($colors, $color_operator)
  {
    global $db;

    if(!empty($colors) && is_array($colors))
    {
      $color_finder = "";
      foreach($colors as $color)
      {
        $color = $db->escape_string($color);

        if(!empty($color_finder))
        {
          switch($color_operator)
          {
            case("XOR"):
            case("AND"):
              $color_finder .= " $color_operator ";
              break;
            default:
              $color_finder .= " OR ";
              break;
          }
        }
        if($color == "Colorless")
        {
          $color_finder .= "(`Card_details`.`Mana_Cost` NOT LIKE \"%Red%\"
                               AND `Card_details`.`Mana_Cost` NOT LIKE \"%Green%\"
                               AND `Card_details`.`Mana_Cost` NOT LIKE \"%Blue%\"
                               AND `Card_details`.`Mana_Cost` NOT LIKE \"%White%\"
                               AND `Card_details`.`Mana_Cost` NOT LIKE \"%Black%\"
                               AND `Card_details`.`Mana_Cost` != \"\"
                            )";
        }
        else
        {
          $color_finder .= "`Card_details`.`Mana_Cost` LIKE \"%".$color."%\"";
        }
      }
      return $color_finder;
    }

    return 1;
  }

  public function createExcludedColorSQL($colors)
  {
    global $db;

    if(!empty($colors) && is_array($colors))
    {
      $color_finder = "";
      foreach($colors as $color)
      {
        $color = $db->escape_string($color);

        if(!empty($color_finder))
        {
          $color_finder .= " AND ";
        }
        if($color == "Colorless")
        {
          $color_finder .= "(`Card_details`.`Mana_Cost` LIKE \"%Red%\"
                               OR `Card_details`.`Mana_Cost` LIKE \"%Green%\"
                               OR `Card_details`.`Mana_Cost` LIKE \"%Blue%\"
                               OR `Card_details`.`Mana_Cost` LIKE \"%White%\"
                               OR `Card_details`.`Mana_Cost` LIKE \"%Black%\"
                               OR `Card_details`.`Mana_Cost` = \"\"
                            )";
        }
        else
        {
          $color_finder .= "`Card_details`.`Mana_Cost` NOT LIKE \"%".$color."%\"";
        }
      }
      return $color_finder;
    }

    return 1;
  }

  public function createManaCostLimitSQL($manacost_low, $manacost_high)
  {
    if(!is_numeric($manacost_low))
    {
      $manacost_low = 0;
    }
    if(!is_numeric($manacost_high))
    {
      $manacost_high = 100;
    }
    return "`Card_details`.`Converted_Mana_Cost` >= $manacost_low AND `Card_details`.`Converted_Mana_Cost` <= $manacost_high ";
  }

  public function createTypeSQL($type)
  {
    global $db;

    $results = explode(" ", $type);
    $type_finder = "";

    foreach($results as $result)
    {
      if($result == "-")
      {
        continue;
      }

      if(!empty($type_finder))
      {
        $type_finder .= " AND ";
      }

      $result = $db->escape_string($result);

      $type_finder .= "`Card_details`.`Types` LIKE \"%$result%\"";
    }

    if(!empty($type_finder))
    {
      return $type_finder;
    }
    return 1;
  }

  public function createCardtextSQL($card_text)
  {
    global $db;

    $card_text = $db->escape_string($card_text);
    return "`Card_text`.`text` LIKE \"%$card_text%\"";
  }

  public function createFlavortextSQL($flavor_text)
  {
    global $db;

    $flavor_text = $db->escape_string($flavor_text);
    return "`Card_details`.`Flavor_text` LIKE \"%$flavor_text%\"";
  }

  public function createRaritySQL($rarities)
  {
    global $db;

    if(empty($rarities))
    {
      return 1;
    }

    $SQL = "";

    foreach($rarities as $rarity)
    {
      $rarity = $db->escape_string($rarity);

      if(!empty($SQL))
      {
        $SQL .= ' OR ';
      }

      $SQL .= '`Card_details`.`Rarity` = "'.$rarity.'"';
    }

    return $SQL; 
  }
  
  public function createCardPowerSQL($power)
  {
    if(empty($power))
    {
      return 1;
    }
  
    return '`Card_details`.`Power` = "'.$power.'"';
  }
  
  public function createCardToughnessSQL($toughness)
  {
    if(empty($toughness))
    {
      return 1;
    }
  
    return '`Card_details`.`Toughness` = "'.$toughness.'"';
  }
  
  public function createCardLoyaltySQL($loyalty)
  {
    if(empty($loyalty))
    {
      return 1;
    }
  
    return '`Card_details`.`Loyalty` = "'.$loyalty.'"';
  }
}

?>
